package cn.clwxy.gdz.service;

import java.util.List;

import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.clwxy.gdz.entity.StudentListPageStudent;
import cn.clwxy.gdz.util.BaseDao;

public class StudentListPageBeanServiceImpl extends BaseDao implements
		StudentListPageBeanService {

	@Override
	public List<StudentListPageStudent> getStudentListPageBy(Object... obj) {
		String sql = "SELECT s.studentId,studentName,studentSex,g.gradeName,i.src,group_concat(sj.subjectName) as subjectName,s.studentDesc FROM `student` s left JOIN grade g ON studentGradeId=g.id INNER JOIN imgid i on i.id=studentImgId left JOIN studentsubjectrrelative ss ON s.studentId=ss.studentId INNER JOIN `subject` sj on sj.subjectId=ss.subjectId "
				+ "WHERE s.studentId in (SELECT studentId from studentsubjectrrelative where subjectId in(SELECT subjectId from `subject` WHERE subjectName like concat('%',?,'%'))) "
				+ "AND s.studentId in(SELECT studentId FROM studentadderss WHERE addressCountyId in (SELECT addressId from addresscounty WHERE addressName like  concat('%',?,'%'))) "
				+ "AND s.studentSex like concat('%',?,'%') "
				+ "AND s.studentGradeId IN(SELECT id FROM grade WHERE gradeName like concat('%',?,'%')) "
				+ "AND s.workType like  concat('%',?,'%') "
				+ "AND s.isDelete=0 "
				+ "GROUP BY s.studentId "
				+ "ORDER BY studentDate DESC LIMIT ?,12";
		return super.queryList(sql,
				new BeanListHandler<StudentListPageStudent>(
						StudentListPageStudent.class), obj);

	}

	@Override
	public Long getStudentCount(Object... obj) {
		String sql = "SELECT count(s.studentId) from( "
				+ "SELECT s.studentId "
				+ "FROM `student` s  "
				+ "INNER JOIN grade g ON studentGradeId=g.id  "
				+ "INNER JOIN imgid i on i.id=studentImgId  "
				+ "INNER JOIN studentsubjectrrelative ss ON s.studentId=ss.studentId  "
				+ "INNER JOIN `subject` sj on sj.subjectId=ss.subjectId  "
				+ "WHERE s.studentId in (SELECT studentId from studentsubjectrrelative where subjectId in(SELECT subjectId from `subject` WHERE subjectName like concat('%',?,'%')))  "
				+ "AND s.studentId in(SELECT studentId FROM studentadderss WHERE addressCountyId in (SELECT addressId from addresscounty WHERE addressName like  concat('%',?,'%')))  "
				+ "AND s.studentSex like concat('%',?,'%')  "
				+ "AND s.studentGradeId IN(SELECT id FROM grade WHERE gradeName like concat('%',?,'%')) "
				+ "AND s.workType  like concat('%',?,'%') "
				+ "GROUP BY s.studentId " + ")as s";
		return super.queryColumn(sql, new ScalarHandler(), obj);

	}

}
